package org.openapi.service.impl;

import com.alibaba.fastjson2.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.openapi.common.ApiException;
import org.openapi.common.ApiUser;
import org.openapi.consts.ApiConst;
import org.openapi.consts.DirectiveConst;
import org.openapi.domain.ApiModel;
import org.openapi.domain.ApiSql;
import org.openapi.parser.QueryParser;
import org.openapi.service.IApiLoaderService;
import org.openapi.service.IDbService;
import org.openapi.service.IOpenApiService;
import org.openapi.service.IOpenExcelService;
import org.openapi.utils.ExcelUtil;
import org.openapi.utils.JsUtil;
import org.openapi.utils.StrUtil;
import org.openapi.vo.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;


/**
 * 导入导出,导入时，需要override参数，如果为true会更新存在的数据，false则在判断重复时报错
 *
 * 解析器业务数据,分三种：
 * 1.原始数据，所见即所得
 * 2.字典数据，需要在代码和标签之间做转换，这个对应关系是固定的，一般在库中保存代码，导入导出的表格中是标签值，导入时需要解析，导出时需要转换
 * 3.关联数据，本业务模型中保存的数据是其它业务模型的主键类数据，在导入导出时也需要做转换，但是这个数据源是不能确定的，需要在转换的时候，查询一次数据库找到对应关系
 */
/**
 * 导入解析
 *  {
 *      "USER": { //主数据示例
 *          "account": "账号",
 *          "name":"姓名",
 *          "phone":"电话",
 *          "sex":"性别",
 *          "dept_id":"部门",
 *          "age": "年龄,
 *      },
 *      "DETAIL":{ //扩展数据
 *         "email":"邮箱",
 *         "address":"地址",
 *         "birthday":"生日"
 *         "id":"@USER.id" //用主数据主键填充
 *      },
 *      "@dict":{
 *          "sex":{"1":"男","2":"女"}
 *      },
 *      "@ds":{
 *          "dept_id":"select dept_id value, dept_name label from sys_dept"
 *      },
 *      "@excel":{
 *       "dept_id":"export"//只导出
 *       "age":"import"//只导入
 *      }
 *  }
 */
@Service
@Slf4j
public class OpenExcelServiceImpl implements IOpenExcelService {
    @Autowired
    private IApiLoaderService apiLoaderService;
    @Autowired
    private IOpenApiService openApiService;
    @Autowired
    private IDbService dbService;

    /**
     * 解析导入配置
     * @param setStr 配置
     * @return
     */
    @Override
    public TableExcel parseImport(String setStr){
        JSONObject params = JSONObject.parseObject(setStr);
        TableExcel excel = new TableExcel();
        Map<String,String> typeMap = new HashMap<>();
        if(params.containsKey(DirectiveConst.DICT)){
            JSONObject dicts = params.getJSONObject(DirectiveConst.DICT);
            for(String dict:dicts.keySet()){
                Map dictData = apiLoaderService.getDict(dicts.getString(dict));
                for(Object k:dictData.keySet()){
                    excel.addDict(dict+":"+dictData.get(k), k.toString());
                }
                typeMap.put(dict,"dict");
            }
            params.remove(DirectiveConst.DICT);
        }

        if(params.containsKey(DirectiveConst.DS)){
            JSONObject dsMap = params.getJSONObject(DirectiveConst.DS);
            for(String ds:dsMap.keySet()){
                String sql = dsMap.getString(ds).trim();

                //引用内置脚本
                if(sql.startsWith("@")){
                    ApiSql apiSql = apiLoaderService.getSql(sql.substring(1));
                    if(apiSql == null){
                        throw new ApiException("数据源不存在");
                    }

                    sql = JsUtil.isFun(apiSql.getContent()) ? JsUtil.exec(apiSql.getContent(), null) : apiSql.getContent();
                }

                //加入数据权限
                ApiUser user = openApiService.getLoginUser();
                sql = sql.replaceAll("\\$\\{userId\\}", user.getUserId());
                sql = sql.replaceAll("\\$\\{orgIds\\}", "'"+ StrUtil.join(user.getOrgIds(), "','")+"'");
                sql = sql.replaceAll("\\$\\{orgId\\}", user.getOrgId());

                List<Map<String,Object>> dsData = dbService.query(sql);
                if(dsData == null || dsData.isEmpty()){
                    continue;
                }
                for(Map row:dsData){
                    excel.addDict(ds+":"+row.get("label").toString(), row.get("value").toString());
                }
                typeMap.put(ds,"ds");
            }
            params.remove(DirectiveConst.DS);
        }
        excel.setDataType(typeMap);
        if(params.containsKey(DirectiveConst.EXCEL)){
            excel.setExcel(params.getJSONObject(DirectiveConst.EXCEL));
            params.remove(DirectiveConst.EXCEL);
        }

        int i=0;
        for(String table:params.keySet()){
            if(table.startsWith("@")){
                continue;
            }
            List<ExcelField> fields = new ArrayList<>();
            JSONObject tableParam = params.getJSONObject(table);
            for(String f:tableParam.keySet()){
                ExcelField field = new ExcelField();
                field.setDataTable(table);
                field.setDataField(f);
                field.setDataLabel(tableParam.getString(f));
                field.setIndex(i++);
                field.setType(typeMap.getOrDefault(f, "val"));
                fields.add(field);
            }
            excel.addTable(table, fields);
        }
        return excel;
    }

    /**
     * 解析导入配置
     * @param setStr 配置
     * @return
     */
    @Override
    public TableExcel parseExport(String setStr){
        JSONObject params = JSONObject.parseObject(setStr);
        TableExcel excel = new TableExcel();
        Map<String,String> typeMap = new HashMap<>();
        if(params.containsKey(DirectiveConst.DICT)){
            JSONObject dicts = params.getJSONObject(DirectiveConst.DICT);
            for(String dict:dicts.keySet()){
                Map dictData = apiLoaderService.getDict(dicts.getString(dict));
                for(Object k:dictData.keySet()){
                    excel.addDict(dict+":"+k.toString(), dictData.get(k).toString());
                }
                typeMap.put(dict,"dict");
            }
            params.remove(DirectiveConst.DICT);
        }

        if(params.containsKey(DirectiveConst.DS)){
            JSONObject dsMap = params.getJSONObject(DirectiveConst.DS);
            for(String ds:dsMap.keySet()){
                String sql = dsMap.getString(ds);
                //引用内置脚本
                if(sql.startsWith("@")){
                    ApiSql apiSql = apiLoaderService.getSql(sql.substring(1));
                    if(apiSql == null){
                        throw new ApiException("数据源不存在");
                    }

                    sql = JsUtil.isFun(apiSql.getContent()) ? JsUtil.exec(apiSql.getContent(), null) : apiSql.getContent();
                }

                //加入数据权限
                ApiUser user = openApiService.getLoginUser();
                sql = sql.replaceAll("\\$\\{userId\\}", user.getUserId());
                sql = sql.replaceAll("\\$\\{orgIds\\}", "'"+ StrUtil.join(user.getOrgIds(), "','")+"'");
                sql = sql.replaceAll("\\$\\{orgId\\}", user.getOrgId());

                List<Map<String,Object>> dsData = dbService.query(sql);
                if(dsData == null || dsData.isEmpty()){
                    continue;
                }
                for(Map row:dsData){
                    excel.addDict(ds+":"+row.get("value").toString(), row.get("label").toString());
                }
                typeMap.put(ds,"ds");
            }
            params.remove(DirectiveConst.DS);
        }
        excel.setDataType(typeMap);
        if(params.containsKey(DirectiveConst.EXCEL)){
            excel.setExcel(params.getJSONObject(DirectiveConst.EXCEL));
            params.remove(DirectiveConst.EXCEL);
        }
        int i=0;
        for(String table:params.keySet()){
            if(table.startsWith("@")){
                log.error("不支持参数 {}", table);
                continue;
            }
            List<ExcelField> fields = new ArrayList<>();
            JSONObject tableParam = params.getJSONObject(table);
            for(String f:tableParam.keySet()){
                ExcelField field = new ExcelField();
                field.setDataTable(table);
                field.setDataField(f);
                field.setDataLabel(tableParam.getString(f));
                field.setIndex(i++);
                field.setType(typeMap.getOrDefault(f, "val"));
                fields.add(field);
            }
            excel.addTable(table, fields);
        }
        return excel;
    }

    /**
     * 导出数据
     * @param paramStr 查询请求
     * @return
     */
    @Override
    public String exports(String paramStr) {
        TableQuery query = QueryParser.parse(paramStr);
        ApiModel model = apiLoaderService.getModel(query.getTable());
        if(model == null){
            throw new ApiException("未定义的数据模型 " + query.getTable());
        }

        List<Map<String,Object>> list = dbService.query(query);

        String fileName = model.getName()+"_"+ StrUtil.date(StrUtil.YYYYMMDDHHMMSS)+".xlsx";

        TableExcel excel = parseExport(model.getExcels());
        Map<String,String> fieldMap = excel.getFieldMap(false);
        for(Map row:list){
            for(String f:fieldMap.values()){
                Object v = row.get(f);
                if(v != null && excel.getType(f) != null){
                    row.put(f, excel.getDict(f+":"+v.toString()));
                }
            }
        }
        try {
            String filePath = openApiService.getUploadPath(ApiConst.EXCEL_FOLDER)+fileName;
            ExcelUtil.exports(fieldMap, list, new FileOutputStream(filePath));
            return fileName;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 导入数据
     * @param inputStream 输入流
     * @param modelCode 数据表
     * @param override
     * @return
     */
    @Override
    public int imports(InputStream inputStream, String modelCode, boolean override) {
        ApiModel model = apiLoaderService.getModel(modelCode);
        if(model == null){
            throw new ApiException("未定义的数据模型 " +modelCode);
        }
        TableExcel excel = parseImport(model.getExcels());
        Map<String,String> fieldMap = excel.getFieldMap(true);
        List<Map<String,Object>> list = ExcelUtil.read(fieldMap, inputStream, 1, 0);
        if(list == null || list.isEmpty()){
            throw new ApiException("未读取到有效数据");
        }
        int rows = 0;
        for(Map<String,Object> row:list){
            rows +=importData(row, excel, override);
        }
        return rows;
    }

    /**
     * 保存一行导入的数据
     * @param row
     * @param excel
     * @param override
     * @return
     */
    private int importData(Map<String,Object> row, TableExcel excel, boolean override){
        Map<String, Map> retMap = new HashMap<>();
        for(String table:excel.getTableMap().keySet()){
            TableData td = new TableData();
            td.setTable(table);
            for(ExcelField field:excel.getTableMap().get(table)){
                String f = field.getDataField();
                Object v = row.get(f);
                if(excel.getType(f) == null){
                    td.addData(f, v);
                }else if(v != null){
                    td.addData(f, excel.getDict(f+":"+v.toString()));
                }
            }
            Map<String, Object> dataMap = td.getData();
            dbService.relData(retMap, dataMap);
            dbService.insertData(td, override);
            retMap.put(table, td.getData());
        }
        return excel.getTableMap().size();
    }


    /**
     * 导入模板
     * @param modelCode 数据表
     * @return
     */
    @Override
    public String template(String modelCode){
        ApiModel model = apiLoaderService.getModel(modelCode);
        if(model == null){
            throw new ApiException("未定义的数据模型 " +modelCode);
        }
        TableExcel excel = parseExport(model.getExcels());
        Map<String,String> fieldMap = excel.getFieldMap(true);
        String fileName = model.getName()+"_导入模板.xlsx";

        try {
            String filePath = openApiService.getUploadPath(ApiConst.EXCEL_FOLDER)+fileName;
            ExcelUtil.template(fieldMap, excel.getDict(), new FileOutputStream(filePath));
            return fileName;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}
